Handling Datasets with Foreign Keys

This topic describes how to handle database tables which are related by Foreign Keys.

If you're not familiar with database structures, a Foreign Key is a column in a database table whose values must only be taken from the set of values to be found in a column in a second database table and where that column in the second table is a Primary Key column. A Primary Key is a column in a database table where each row must have a different value within that column and hence allows each row within the table to be uniquely differentiated from any other row in the table. For example, when storing customer details, a customer number could act as a Primary Key, since each customer is given a different number to uniquely identify them. Within a purchase order and accounts systems, the customer number could be used to identify each customer so you don't have to repeat all their details for each order or payment etc. In this case, the customer number column in the payments and orders systems could be set up as a Foreign Key linked back to the customer details table to make sure that only valid customer numbers are entered into these systems.

Some database tables combine multiple columns to form the table's Primary Key, which means that each column doesn't have to have unique values in each row but, when combined, the values from each of the Primary Key columns within a row will form a unique combination which allows each row to be distinguished from all the other rows. If a table has a Primary Key which is made up of a collection of columns, the equivalent Foreign Key in another table would need the same set of columns as the Primary Key to which it is linked.

A Primary Key table is called a referenced or parent table and a related Foreign Key table is known as a child table.

 

 

Where you have Datasets with Foreign Key relationships, Digitise Forms distinguishes between the following situations:

  • Single Foreign Key Relationships

    A Datasource contains one or more database tables which are linked by the same Foreign Key relationship to a single parent table in the same Datasource.

    Neither the child tables nor the parent have any other Foreign Key relationships with any other tables. The Primary Key and Foreign Key consist of only a single column.

  • Multiple Foreign Key Relationships

    A Datasource contains one or more child tables which have multiple Foreign Keys defined within them, a child Dataset has multiple parent Datasets in the Datasource and/or a Primary Key consists of multiple columns.

 

The presence of Foreign Key relationships between Datasets in a Datasource provides opportunities to handle the data in the related Datasets together, e.g. to download data in a child Dataset at the same time as the parent Dataset. Form Studio's default behaviour and the options available to you differ between the two types of Foreign Key Relationship above.

  • The situation with Foreign Key relationships also applies where you add one or more Recordset Elements to a form, define their Datasets as static columns and then use New Dataset from Form to create the Datasets (see Map Data to Elements). All Recordsets linked to the form Dataset in this way, constitute a Single Foreign Key relationship and will be treated as described below under Configure and use Automatic Data Management with Single Foreign Key Relationships.

 

  • The contents of this topic are not relevant where you are using an Imported Stored Procedure. In this scenario, you would need to handle any Foreign Key relationships within the Stored Procedure, if needed.

 

 

If you have two or more Datasets which are related by a single Foreign Key relationship, records in the child Dataset(s) can be fully managed by the parent Dataset, which provides the following behaviour:

  • Reading from or writing to the parent Dataset will automatically read or write the child Dataset at the same time.

    For example, you can download or update both parent and child Datasets by a single call to loadDatasource or updateDatasource within an Event or custom JavaScript. In either case, you only need to specify the parent Dataset within the function call and the child Dataset will automatically be included as well; you don't need to call the function a second time for the child Dataset. If you want the Datasets to be download by the form, you only need to configure the parent Dataset to be downloaded.

    When data is downloaded, the child data is downloaded with the parent data and then moved into the child Dataset(s) so that it becomes available to Elements input mapped to the child Dataset(s).

    The code required to read and write the child Dataset(s) is not included in the form; the child data is retrieved and updated within the context of the related parent data.

  • When displaying data from the child Dataset(s) on your form, Digitise Forms will automatically select the record(s) in the child Dataset(s) where the Foreign Key value matches the Primary Key value of the current record in the parent Dataset.

    When the current record in the parent Dataset changes, e.g. when calling the moveFirst, moveNext etc. functions in an Event or custom JavaScript, the selected record(s) in the child Dataset(s) will automatically change to match the new Primary Key in the parent Dataset and input mapped Elements on the form will automatically be updated. This feature is really intended for use with the Recordset Element.

  • When adding new records, the Foreign Key field in new child Dataset records will automatically be set to match the Primary Key of the related record in the parent Dataset.

    If you use the createDatasource function in custom JavaScript to create a new record, you only need to specify the parent Dataset, the related child Dataset will be handled automatically.

  • Reading and writing the child Dataset(s) can be configured separately, so, for example, you can upload child records with the parent records without having to download the child data with the parent data. You can also configure individual children to be read with the parent data or not, so you can just download those child Datasets you need. When updating data, however, all child Datasets will be updated with the parent data.

 

This behaviour is controlled by two Dataset properties:

This property determines whether child Datasets should be updated with the parent Dataset or not. It can be found on the Settings tab in the Properties Pane in the properties for the parent Dataset:

Displays an image of the Dataset Properties Pane showing the Manage Related Data property

If this property is selected:

  • The Foreign Key field in the child Dataset(s) will be automatically populated with the Primary Key of the parent's current record, providing the Primary and Foreign Keys have a data type of GUID (uniqueidentifier) or int with the Identity property set to True. Updating the parent Dataset will automatically update the child Dataset(s) as well.

If the property is not selected:

  • Child Datasets will not be updated when the parent Dataset is updated using updateDataSource and the Foreign Key field will not be automatically populated. You will need to make sure that the correct value is entered into all relevant Foreign Key fields before the form is submitted or the Dataset is updated using updateDatasource.

 

Whether child data is downloaded with the parent data, depends upon the setting of the Download with Parent property, below.

 

This property determines whether a child Dataset should be downloaded with its parent Dataset or not. It can be found under the Mappings tab in the Form Design workspace area when you have a Dataset open.

For example, suppose you have CustomerID, ContactDetails and PurchaseOrder tables, where the Primary Key of the Customer ID table is the customer's unique Customer Number and both ContactDetails and PurchaseOrder tables have a Foreign Key field containing the Customer Number. In your form, you might want to enter a CustomerID and then list all the purchase orders relating to that customer, but not download all customers' sensitive personal details held in the ContactDetails table. The Download with Parent property allows you to download the PurchaseOrders table with the CustomerID table so that the purchase orders would be automatically aligned with the relevant customer ID but only download contact details for individual customers as and when you actually need them, rather than downloading the information for all customers at once.

 

The column list under the Mappings tab includes a Download with Parent column where you can select or deselect the property for individual columns in the Dataset:

Displays the Dataset Mappings tab showing the Download With Parent options

This property is only relevant to Foreign Key columns, such as the CustomerId column in the picture above. For any other columns the property is not relevant and is disabled and unselected, as in the PONumber column above.

 

If the property is selected:

  • The Dataset in which the property is selected will automatically be downloaded when the corresponding parent Dataset is downloaded, i.e. when the Dataset which contains the Primary Key, that this Foreign Key is linked to, is downloaded. This means that you only need to select the Loads With Form property or call loadDatasource for the parent Dataset, the child Dataset will automatically be downloaded with the parent data. In addition, you only need to select the Allow Read Access property for the parent Dataset; child Datasets will inherit this and various other property values from the parent Dataset.

  • After being downloaded, the child data will be moved into the child Dataset so it becomes available to Elements input mapped to that child Dataset and these Elements will automatically be updated with the appropriate values from the child record(s) where the Foreign Key field value matches the Primary Key value of the parent's current record.

  • When the current record in the parent Dataset changes, e.g. when calling the moveFirst, moveNext etc. functions in an Event or custom JavaScript, the selected record(s) in the child Dataset will automatically change to match the new Primary Key in the parent Dataset and input mapped Elements on the form will automatically be updated.

 

For example, in the picture above, if you want the MyPurchaseOrders table to be downloaded whenever the CustomerID table is downloaded, you would select the property for the CustomerId column in the Dataset:

Picture showing Dataset mappings Tab with Download With Parent option selected

If the Download with Parent property is not selected:

  • The child Dataset won't be downloaded with the parent data and you will need to download the child data separately, if you want to access it within your form. For example, if you want both the parent and child Datasets to be downloaded by the form, you would need to separately select the Loads With Form property and the Allow Read Access property for both Datasets.

 

Whether child data is uploaded with the parent data, depends upon the setting of the Submit Related Data property, above.

  • If you have upgraded a project created using Digitise Forms v1.4 or earlier, the Download with Parent property will be unselected unless the parent Dataset had its Manage Related Data property selected or the project was created before the introduction of the Manage Related Data property, in either of which cases, Download with Parent will be selected so that your upgraded project works as it did before.

 

 

By default, when you create a Datasource which contains two or more Datasets linked by a single Foreign Key relationship, the Submit Related Data property will be selected in the parent Dataset but the Download with Parent property will be deselected for the corresponding Foreign Key column(s) in the child Dataset(s). This will result in the child Dataset(s) being uploaded with the parent Dataset but not downloaded with the parent Dataset. Any child Datasets you want to be downloaded with the parent, will need to have their Download with Parent property selected manually.

 

The parent Dataset also has the following standard Dataset properties (as appropriate): Loads With Form, Allow Write Access, Max Write Requests, Server-side validation, Sanitize data when storing and Update with Stored Procs, which you will need to configure as normal. The values you select here for these properties also apply to the child Dataset - the properties will be hidden and are not configurable under the child Dataset itself.

The Allow Read Access and Max Read Requests properties, however, appear under both the parent and child Dataset(s). If you select Download with Parent for a Child Dataset, these properties will be hidden and the values will be inherited from the parent Dataset but if Download with Parent is deselected, the properties will be displayed allowing you to configure them in the event you want to download data for this Dataset separately from the parent data. In order to reshow these properties after deselecting Download with Parent, you will need to close the Dataset and re-open it.

 

All Datasets also have a property called Is Updated with Parent. This property is automatically selected in a child Dataset when its parent Dataset's Submit Related Data property is selected, to indicate that data uploads are being managed by the parent Dataset. It will be deselected in the parent Dataset. Below this property in the properties for a child Dataset, you will find the Parent Dataset Name property. This property displays the name of the parent Dataset when the Is Updated with Parent property is selected. These properties are for information only and cannot be edited.

If, when Submit Related Data is selected, you want to create a new record in the child Dataset without creating an equivalent new record in the parent Dataset, you will need to make sure the parent's current record contains the Primary Key value matching the value you want to insert into the Foreign Key field in the child record. You can do things like use the parent Dataset's SelectByKey SQL query to select the required record or use the datasource navigation actions, MoveFirst, MoveNext, MovePrevious and MoveLast within an Event or custom JavaScript to move through the parent records to the required record.

 

If you don't want the child data to be managed by the parent Dataset, you can deselect one or other or both of the Submit Related Data and Download with Parent properties, depending upon the behaviour you want.

If you deselect the Submit Related Data property on the parent Dataset, the Is Updated with Parent property under all related child Datasets will automatically be deselected as well. The Parent Dataset Name property won't be displayed under the child Dataset(s) and the Loads With Form, Allow Write Access, Max Write Requests, Server-side validation, Sanitize data when storing and Update with Stored Procs properties will all become available and can be set independently of the parent Dataset. You will need to handle uploading the parent and child Datasets individually within the form just as you would in the case of multiple Foreign Key relationships (see below).

Deselecting Download with Parent for a child Dataset means that that child data will no longer be downloaded with the parent data and will need to be downloaded separately, if required. If you want to include the child data with the parent data, you can reselect the Download with Parent property in the child Dataset.

 

Note that you can have more than one Datasource with single Foreign Key relationships coexisting on a form with each one acting independently of the others and each one behaving as described above.

 

If you have Datasets in a Datasource which are related by multiple Foreign Key relationships, records in the child Datasets may not be able to be managed by the parent Dataset in the same way as single Foreign Key relationships:

  • The Submit Related Data property is selected in the parent Dataset's properties but within its child Datasets' properties the Is Updated with Parent property is unselected and disabled, and the Parent Dataset Name property is hidden.

  • Child Datasets have the Download with Parent property enabled but not selected for Foreign Key fields which are related to other Datasets in the Datasource. Foreign Key fields relating to database tables which haven't been included in the Datasource are not identified and don't have the Download with Parent property enabled. This means that, by default, child Datasets won't be downloaded together with their parent data.

  • Child Datasets have the Loads With Form, Allow Read Access, Max Read Requests, Allow Write Access, Max Write Requests, Server-side validation, Sanitize data when storing and Update with Stored Procs properties available (as appropriate) and these can be set separately for each Dataset.

 

In terms of uploading records created or modified by the form, in some situations you may still be able to upload child records with the parent records. For example, if you have a child database table which has Foreign Key relationships to multiple parents but you create a Datasource which only contains one of the parent tables and the child table, selecting Submit Related Data may still work for that Datasource and allow child records to be updated with the parent record, providing you supply appropriate Foreign Key field values for the child Dataset for those Foreign Key fields not represented by a parent Dataset.

In other situations, e.g. where you have child Datasets with multiple Foreign Key relationships and multiple parent Datasets in the same Datasource or a parent Dataset with multiple child Datasets where one or more of the children have multiple Foreign Key fields, updating child records cannot be handled with the parent records and you will need to deselect the Submit Related Data property on any affected parent Datasets.

In these situations, you will need to handle each Dataset within the relationship individually within your form in terms of uploading data. For example, a purchase order record could have two Foreign Key fields relating it to both a product code table and a customer details table. If you want to upload records to all three tables from your form, you could use the loadDatasource function three times, once for each Dataset, within an Event or custom JavaScript.

If you are creating a new record for a Dataset with one or more Foreign Keys you will need to arrange for the appropriate Foreign Key values to be assigned to the new record, these won't be assigned automatically. This means that you will have to ensure that any Primary Key values referenced in these Foreign Key fields already exist in the parent database table(s) before the new child record is submitted. For an example of this, see Code Sample 3 under the description of the updateDatasource function.

 

In terms of downloading parent and child records together, by default child records won't be downloaded with their corresponding parent records. However, you can change this default behaviour to provide automatic downloading of child Datasets with parent Datasets if you want to, using the Download with Parent property. If you select Download with Parent for a Dataset column, the Dataset in which that column appears will be automatically downloaded when the parent Dataset, that the Foreign Key field relates to, is downloaded. You will still need to deal with each Dataset separately when uploading Datasets and handle Foreign Keys as described above, but you won't need to configure the child Dataset to be loaded by the form or make multiple calls to loadDatasource. Remember that child Datasets will only identify Foreign Key fields that relate to parent tables contained in the same Datasource.

If you don't select the Download with Parent property for any Foreign Key fields in a Dataset, that Dataset won't be downloaded with any corresponding Parent Dataset(s) and you will need to handle downloading the Dataset and navigating through records within it separately from other Datasets. For example, a purchase order record could have two Foreign Key fields relating it to both a product code table and a customer details table. If you want to download the data from all three tables to your form, you could configure each of the three Datasets separately to be loaded by the form or use the loadDatasource function three times, once for each Dataset, within an Event or custom JavaScript (or some combination of these two methods).

 

For more information about the Submit Related Data and Download with Parent properties refer to the section above: Configure and use Automatic Data Management with Single Foreign Key Relationships.

 

If you have a parent Dataset which has a mixture of single Foreign Key relationships and multiple Foreign Key relationships with other Datasets in the Datasource, you can still allow single Foreign Key relationships to be managed by the parent Dataset. In this situation, you wouldn't want to deselect the Submit Related Data property in the parent but you would have to make sure that there are no output mappings to the multiple Foreign Key relationship Dataset(s) otherwise uploads will fail. In general, in this type of scenario we would recommend creating separate Datasources for single and multiple Foreign Key relationship Datasets rather than including them in the same Datasource.